package Dao;


import entity.OrdersBean;
import util.JDBCUtil;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.List;

public class OrdersDao {
    /**
     * 获取所有订单信息
     * */
    public List<OrdersBean> getOrdersAll(){
        List<OrdersBean> list = null;
        String sql = "select order_id,order_No,courts_table,courts_type_name,member_phone,total_amount,payment_state," +
                "order_time,available_time from (SELECT order_id,order_No,courts_table,courts_type_id," +
                "member_phone,total_amount,payment_state,order_time,available_time " +
                "FROM Orders,courts,member_information WHERE orders.court_id=courts.court_id " +
                "and orders.member_id=member_information.member_id  " +
                "ORDER BY payment_state DESC) as table1,courts_type where table1.courts_type_id " +
                "= courts_type.courts_type_id";
        list = JDBCUtil.list(sql, OrdersBean.class);
        return list;
    }
    /**
     * 通过id获取指定订单信息
     * */
    public OrdersBean getOrderID(int order_id){
        OrdersBean order = null;
        String sql = "select * from Orders where order_id=?";
        order = JDBCUtil.one(sql,OrdersBean.class,order_id);
        return order;
    }
    /**
     * 添加一个订单
     * */
    public int addOrders(OrdersBean order) throws SQLException {
        int num = 0;
        String sql = "INSERT INTO Orders(order_No,court_id,member_id,total_amount,payment_state,order_time,available_time) VALUES(?,?,?,?,?,?,?);";
        PreparedStatement pstmt = JDBCUtil.getCon().prepareStatement(sql);
        pstmt.setString(1, order.getOrder_No());
        pstmt.setInt(2, order.getCourt_id());
        pstmt.setInt(3, order.getMember_id());
        pstmt.setInt(4, order.getTotal_amount());
        pstmt.setString(5, order.getPayment_state());
        pstmt.setTimestamp(6, order.getOrder_time());
        pstmt.setNull(7, java.sql.Types.TIMESTAMP);
        num = pstmt.executeUpdate();
        return num;
    }
    /**
     * 修改订单的可用时间和球桌号
     * */
    public int updateOrders(int court_id,Timestamp available_time,int order_id){
        OrdersBean ordersBean = new OrdersBean();
        int num = 0;
        String sql = "update Orders set court_id=?,available_time=? where order_id=?";
        num = JDBCUtil.update(sql,ordersBean.getCourt_id(),ordersBean.getAvailable_time(),ordersBean.getOrder_id());
        return num;
    }
    /**
     * 修改支付状态
     * */
    public int updatePayment(String payment_state,int order_id){
        OrdersBean ordersBean = new OrdersBean();
        int num = 0;
        String sql = "update Orders set payment_state=? where order_id=?";
        num = JDBCUtil.update(sql,ordersBean.getPayment_state(),ordersBean.getOrder_id());
        return num;
    }
    /**
     *根据台球类型，支付状态，台球号
     * */
    public List<OrdersBean> searchOrders(String courts_type_name,String payment_state,String courts_table){
        List list = new ArrayList();
        String sql = "select * from (select order_id,order_No,courts_table,courts_type_name,member_phone,total_amount,payment_state,order_time,available_time from (SELECT order_id,order_No,courts_table,courts_type_id,member_phone,total_amount,payment_state,order_time,available_time FROM Orders,courts,member_information WHERE orders.court_id=courts.court_id and orders.member_id=member_information.member_id) as table1,courts_type where table1.courts_type_id = courts_type.courts_type_id) table2 where 1=1";
        if (TextUtils(courts_table)){
            sql = sql + " and courts_table like ?";
            list.add("%" + courts_table + "%");
        }
        if (TextUtils(courts_type_name)){
            sql = sql + " and courts_type_name = ?";
            list.add(courts_type_name);
        }
        if (TextUtils(payment_state)){
            sql = sql + " and payment_state = ?";
            list.add(payment_state);
        }
        if (list.size() == 1){
            return JDBCUtil.list(sql,OrdersBean.class,list.get(0));
        }else if(list.size() == 2){
            return JDBCUtil.list(sql,OrdersBean.class,list.get(0),list.get(1));
        }else if (list.size() == 3){
            return JDBCUtil.list(sql,OrdersBean.class,list.get(0),list.get(1),list.get(2));
        }
        return JDBCUtil.list(sql,OrdersBean.class);
    }

    /**
     * 修改支付状态
     * */
    public int doSettle(int uuid,Timestamp now,int total_amount){
        int num = 0;
        String sql = "update Orders set available_time=?,payment_state='已支付',total_amount=? where order_id=?";
        num = JDBCUtil.update(sql,now,total_amount,uuid);
        return num;
    }

    /**
     * 通过球桌号获取该球桌号正在进行的订单
     * */
    public OrdersBean getOrderByCourt_id(int court_id){
        OrdersBean order = null;
        String sql = "select order_id,order_No,court_id,member_id,total_amount,payment_state,order_time,available_time from Orders where court_id=? and payment_state='未支付'";
        order = JDBCUtil.one(sql,OrdersBean.class,court_id);
        return order;
    }

    //判断是否为null
    public boolean TextUtils(Object obj){
        return obj != null && !"".equals(obj) && !"所有".equals(obj);
    }
    public static void main(String[] args) {
        List<OrdersBean> ordersBeans = new OrdersDao().searchOrders("", "" , "1-1");
        System.out.println(ordersBeans);
        Timestamp now = Timestamp.valueOf(LocalDateTime.now(ZoneId.systemDefault()));
//        int num = new OrdersDao().doSettle(38,now,30);
//        System.out.println(num>0?"修改成功":"修改失败");
    }
}
